package cn.lw.access;


import cn.lw.model.EemPpPcbAoi;
import cn.lw.utils.AccessUtil;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class AccessConnection {

    @Test
    public void Con1() throws Exception {
        //建立连接
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ="+"e://TestData.mdb";
        Connection con = DriverManager.getConnection(url);
        String sql = "select * from DNB_SC_AOI";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        List<EemPpPcbAoi> eemPpPcbAois = new ArrayList<>();
        if (resultSet.next()){
            EemPpPcbAoi eemPpPcbAoi = new EemPpPcbAoi();
            eemPpPcbAoi.setId(resultSet.getInt("ID"));
            eemPpPcbAois.add(eemPpPcbAoi);
        }
        log.info("result【{}】",eemPpPcbAois);
    }

    @Test
    public void Con2() throws Exception {
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        Connection con = DriverManager.getConnection("jdbc:ucanaccess://" + "e://TestData.mdb");
        Statement st = con.createStatement();
        DatabaseMetaData dbmd = con.getMetaData();
        ResultSet rstable = dbmd.getTables(null, null, "%", null);
        while (rstable.next()) {
            String sql = "select * from DNB_SC_AOI";
            ResultSet rs = st.executeQuery(sql);
            List<EemPpPcbAoi> eemPpPcbAois = new ArrayList<>();
            while (rs.next()) {
                EemPpPcbAoi eemPpPcbAoi = new EemPpPcbAoi();
                eemPpPcbAoi.setId(rs.getInt("ID"));
                eemPpPcbAois.add(eemPpPcbAoi);
            }
            log.info("result【{}】", eemPpPcbAois);

        }
    }
    @Test
    public void Con3() {
        String path = "e://TestData.mdb";
        String sql = "select * from DNB_SC_AOI";


        List<EemPpPcbAoi> list = AccessUtil.Con2(path, sql, (rs)  ->{
                EemPpPcbAoi eemPpPcbAoi = new EemPpPcbAoi();
                try {
                    eemPpPcbAoi.setId(rs.getInt("ID"));
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                return eemPpPcbAoi;
        });
        log.info("结果集【{}】",list);
    }

}
